package org.anyline.simple.oracle;

import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.jdbc.oracle.OracleAdapter;
import org.anyline.data.jdbc.util.DataSourceUtil;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.*;
import org.anyline.metadata.*;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.*;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

@SpringBootTest
public class OracleTest {
    private Logger log = LoggerFactory.getLogger(OracleTest.class);
    @Autowired
    private AnylineService service          ;
    @Autowired
    private JdbcTemplate jdbc               ;
    private Catalog catalog = null          ; // ORACLE不支持
    private Schema schema   = null          ; // ORACLE中可以相当于数据库名
    private String table    = "CRM_USERS"    ; // 表名

    @Test
    public void cols() throws Exception{
        service.metadata().table("am_rel_track_doc");
    }
    @Test
    public void upsert() throws Exception{
        DataRow row = new DataRow();
        row.put("code", "a");
        row.put("name","z");
        ConfigStore configs = new DefaultConfigStore();
        DataSet set = new DataSet();
        set.add(row);
        configs.override(true, "code");

        service.insert("crm_user", set, configs);
    }

    @Test
    public void test() throws Exception{
        String url = "jdbc:oracle:thin:@localhost:31521/FREE";
        DataSource ds1 = DataSourceUtil.build("com.zaxxer.hikari.HikariDataSource", "oracle.jdbc.OracleDriver", url, "SYSTEM", "123456");
        AnylineService service = ServiceProxy.temporary(ds1);
        Table table = new Table("t_"+System.currentTimeMillis());
        table.addColumn("ID", "int");
        service.ddl().create(table);
    }
    @Test
    public void columnPk() throws Exception {
        ConfigTable.IS_METADATA_AUTO_CHECK_COLUMN_PRIMARY = true;
        LinkedHashMap<String, Column> columns = service.metadata().columns("CRM_USER11S");
        for(Column column:columns.values()){
            System.out.println(column.getName()+":"+column.isPrimaryKey());
            System.out.println(column.getMetadata());
        }
    }

    @Test
    public void batch() throws Exception{
        Table tab = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "bigint", false, null).setComment("主键").autoIncrement(true).primary(true);
        tab.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        tab.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        service.ddl().save(tab);
        long id = System.currentTimeMillis();
        DataSet set = new DataSet();
        //插入10行
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            row.put("ID", id + i);
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "N_"+i);
            set.add(row);
        }
        service.insert(table ,set);
        set = new DataSet();
        for(int i=0; i<15; i++){
            DataRow row = new DataRow();
                row.put("ID", id + i);
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "U_N"+i);
            set.add(row);
        }
        //save 15行，其中10行update 5行insert
        service.save(100,table ,set);
    }

    @Test
    public void index() throws Exception{
        Table tab = init();
        Index idx = tab.getIndex("IDX_ID_CODE_NAME");
        if(null == idx){
            idx = new Index("IDX_ID_CODE_NAME");
            idx.addColumn("ID", "ASC");
            idx.addColumn("CODE", "DESC");
            idx.addColumn("NAME", "DESC");
            idx.setTable(tab);
            service.ddl().add(idx);
        }
        tab = service.metadata().table(table);
        LinkedHashMap<String, Index> indexes = tab.getIndexes();
        for(Index index:indexes.values()){
            System.out.println("\nindex:"+index.getName());
            LinkedHashMap<String, Column> columns = index.getColumns();
            for(Column column:columns.values()){
                System.out.println("column:"+column.getName() + " position:"+index.getPosition(column.getName()) + " order:"+index.getOrder(column.getName()));
            }
        }
        service.ddl().drop(tab);
        service.ddl().create(tab);
    }
    private Table init() throws Exception{
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        //service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(catalog, schema, this.table);

        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "INT", false, null).setComment("主键").autoIncrement(true).primary(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号").setDefaultValue("A01");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称").setDefaultValue("zh");
        table.addColumn("AGE", "INT").setComment("年龄").setDefaultValue(20);
        table.addColumn("remark", "NCLOB(111)").setComment("注册日期"); //不小心设置了长度应该自动忽略
        table.addColumn("REG_DATE", "DATE").setComment("注册日期");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("REG_TIME2", "datetime");
        table.addColumn("DATA_VERSION", "DECIMAL(10,2)", false, 1.1).setComment("数据版本");

        //创建表
        service.ddl().create(table);
        return table;
    }


    @Test
    public void pk_auto() throws Exception {
        OracleAdapter.IGNORE_SCHEMAS = "";
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if (null != table) {
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        //service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(catalog, schema, this.table);
        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "INT", false, null).setComment("主键").setAutoIncrement(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("DATA_VERSION", "double", false, 1.1).setComment("数据版本");
        table.setPrimaryKey("ID");
        service.ddl().create(table);

        table = service.metadata().table(catalog, schema, this.table);
        LinkedHashMap<String,Column> columns = table.getColumns();
        for (Column column:columns.values()){
            System.out.println(column.getName());
            System.out.println(column.getFullType());
            System.out.println(column.isAutoIncrement());
            System.out.println(column.isPrimaryKey());
        }
    }
    @Test
    public void defaultDate() throws Exception{
        Table table = init();
        table.getColumn("REG_TIME2").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        service.ddl().save(table);
    }
    @Test
    public void float_double() throws Exception{
        type("float");          //float        异常
        type("float(32)");      //float(32)    正常
        type("float(10)");      //float(10)    正常
        type("float(10,2)");    //float(10,2)  异常
        type("double");         //double       异常(会换成number)
        type("double(32)");     //double(32)   异常(会换成number)
        type("double(10)");     //double(10)   异常(会换成number)
        type("double(10,2)");   //double(10,2) 异常(会换成number)
    }
    private void type(String type){
        try {
            String sql = "CREATE TABLE TAB_" + System.currentTimeMillis() + "(id int, code "+type+")";
            service.execute(sql);
            System.out.println(LogUtil.format(type + " 正常", 32));
        }catch (Exception e){
            System.out.println(LogUtil.format(type + " 异常", 31));
        }
    }

    @Test
    public void entity() throws Exception{
        User user = new User();
        user.setId(1000);
        user.setName("张三");
        ConfigTable.IS_UPDATE_NULL_FIELD = false;
        service.save("crm_user", user);
    }

    @Test
    public void interval() throws Exception{
        DataSet set = service.querys("c_test");
        System.out.println(set.getRow(0).get("SSS"));
    }

    @Test
    public void tabQuery() throws Exception{
        Table table = service.metadata().table("crm_user", false);
        if(null == table){
            table = new Table("crm_user");
            table.addColumn("ID","INT");
            service.ddl().create(table);
        }
        table = service.metadata().table(false, new Catalog(null), new Schema(null), "crm_user",false);
        System.out.println(table);
    }

    @Test
    public void querys() throws Exception{
        DataRow row = new DataRow();
        row.put("ID", System.currentTimeMillis());
        row.put("CODE", System.currentTimeMillis());
        service.insert("CRM_USER", row);
        DataSet set = service.querys("SELECT A.TABLE_NAME TABLE_NAME, B.COMMENTS TABLE_COMMENT FROM USER_TABLES A, USER_TAB_COMMENTS B WHERE A.TABLE_NAME = B.TABLE_NAME ORDER BY TABLE_NAME");
        System.out.println(set);
    }


    @Test
    public void pk1() throws Exception{
        Table table = service.metadata().table(this.table, false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true);
        table.addColumn("CODE", "int");
        service.ddl().create(table);

        service.ddl().save(table);

        PrimaryKey pk = new PrimaryKey();
        pk.setName("pk_code");
        pk.addColumn("CODE");
        table.setPrimaryKey(pk);
        service.ddl().save(table);
       // service.ddl().add(pk);
    }
    @Test
    public void pks() {
        LinkedHashMap<String,Table> tabs = service.metadata().tables();
        String tableName = "t_test";
        Table dbTable = service.metadata().table(tableName);
        Table table = Objects.isNull(dbTable) ? new Table(tableName) : dbTable;
        table.setComment("测试表");

        Column id = table.getColumn("id");
        if (Objects.isNull(id)) {
            id = new Column("id", "bigint");
            id.setPrimary(true).setAutoIncrement(true).setNullable(false).setComment("主键ID");
            table.addColumn(id);
        }
        else {
            id.setComment("主键ID修改");
        }

        Column name = table.getColumn("name");
        if (Objects.isNull(name)) {
            name = new Column("name", "varchar").setPrecision(100).setComment("名称");
            table.addColumn(name);
        }
        else {
            name.setType("varchar").setPrecision(150).setComment("名称");
        }

        Column code = table.getColumn("code123456789012345");
        if (Objects.isNull(code)) {
            code = new Column("code123456789012345", "varchar").setPrecision(50).setComment("编码");
            table.addColumn(code);
        }
        else {
            code.setType("varchar").setPrecision(50).setComment("编码");
        }

        Column age = table.getColumn("age123456789012345");
        if (Objects.isNull(age)) {
            age = new Column("age123456789012345", "int").setComment("年龄");
            table.addColumn(age);
        }
        else {
            age.setType("int").setPrecision(11).setComment("年龄");
        }

        Column number = table.getColumn("number1234567890123");
        if (Objects.isNull(number)) {
            number = new Column("number1234567890123", "decimal").setPrecision(10).setScale(3).setComment("数量");
            table.addColumn(number);
        }
        else {
            number.setType("decimal").setPrecision(10).setScale(3).setComment("数量");
        }

        try {
            service.ddl().save(table);
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    public void pk() throws Exception{
        Table table = service.metadata().table(this.table, true);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true);
        table.addColumn("CODE", "int").setComment("编号");
        service.ddl().create(table);
        table = service.metadata().table(this.table);

        PrimaryKey pk = new PrimaryKey();
        pk.setName("pk_code");
        pk.addColumn("CODE");
        table.setPrimaryKey(pk);
        service.ddl().save(table);
    }
    @Test
    public void sequence(){
        LinkedHashMap<String,Sequence> sequences = service.metadata().sequences();
        for(Sequence sequence:sequences.values()){
            System.out.println(BeanUtil.object2json(sequence));
        }
    }
    @Test
    public void version() throws SQLException {
        String name = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductName();
        String version = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductVersion();
        String catalog = jdbc.getDataSource().getConnection().getCatalog();
        String schema = jdbc.getDataSource().getConnection().getSchema();
        log.warn("\nname:{}\nversion:{}\ncatalog:{}\nschema:{}",name, version, catalog, schema);
    }

    @Test
    public void info() {
        log.warn("\ntype:{}\ncatalog:{}\nschema:{}\ndatabase:{}\nproduct:{}\nversion:{}"
                ,service.metadata().type()
                ,service.metadata().catalog()
                ,service.metadata().schema()
                ,service.metadata().database()
                ,service.metadata().product()
                ,service.metadata().version()
        );
    }


    @Test
    public void databases() throws SQLException {
        LinkedHashMap<String, Database> databases = service.metadata().databases();
        for(Database database:databases.values()){
            log.warn("数据库:{}", database.getName());
        }
    }

    @Test
    public void tables() throws Exception{
        LinkedHashMap<String,Table> tables = service.metadata().tables("TAB%", 1);
        for(Table table:tables.values()){
            service.metadata().table(table.getCatalog(), table.getSchema(), table.getName());
        }
    }
    @Test
    public void autoIncrement() throws Exception{
        Table table = service.metadata().table("TAB_AUTOINCREMENT");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("TAB_AUTOINCREMENT");
        table.addColumn("ID1","BIGINT").setAutoIncrement(true);
        table.addColumn("ID2","BIGINT").setAutoIncrement(true);
        service.ddl().create(table);
    }

    @Test
    public void blob() throws Exception{

        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //定义表结构
        table = new Table(catalog, schema, this.table);

        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
      //  table.addColumn("ID", "INT", false, null).setComment("主键").autoIncrement(true).primary(true);
        table.addColumn("CODE", "BLOB(50)").setComment("编号");
        service.ddl().create(table);
    }
    @Test
    public void ddl() throws Exception{
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = init();

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNotNull(table);

        table.getColumn("NAME").setType("blob");
        service.ddl().save(table);
        if(null != service.query("USER_SEQUENCES","SEQUENCE_NAME:SIMPLE_SEQ")) {
            service.execute("DROP SEQUENCE SIMPLE_SEQ");
        }
        String sql = "CREATE SEQUENCE SIMPLE_SEQ MINVALUE 0 START WITH 0 NOMAXVALUE INCREMENT BY 1 NOCYCLE CACHE 100";

        service.execute(sql);

    }
    @Test
    public void metadata() throws Exception{
        List<Table> tables = service.metadata().tables(true);
        for(Table table:tables){
            System.out.println(table.getCatalog()+"_"+table.getSchema()+"_"+table.getName());
        }
    }

    @Test
    public void override() throws Exception{
        DataSet set = service.querys("CRM_USER",0,2);
        service.insert(table, set, new DefaultConfigStore().override(true, "CODE"));
        service.insert(table, set, new DefaultConfigStore().override(false, "CODE"));
    }
    @Test
    public void delimiter() throws Exception{
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        Table tab = service.metadata().table("b-test");
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table("b-test");
        tab.addColumn("ID", "BIGINT");
        tab.addColumn("CODE","VARCHAR(10)");
        tab.addColumn("A-C", "VARCHAR(19)");
        tab.setPrimaryKey("ID");
        service.ddl().create(tab);
        service.querys("b-test");
        DataRow row = new DataRow();
        row.put("ID", System.currentTimeMillis());
        service.insert("b-test", row);
        DataSet set = new DataSet();
        long fr = System.currentTimeMillis();
        for(int i=0;i<10; i++){
            DataRow item = new DataRow();
            item.put("ID", fr+i);
            item.put("CODE", i);
            item.put("A-C", i);
            set.add(item);
        }
        service.insert("b-test", set);
    }
    @Test
    public void dml() throws Exception{
        long qty = 0;
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        DataSet set = null;
        DataRow row = null;

        //序列查询
/*

        BigDecimal next = service.sequence("SIMPLE_SEQ");
        DataRow nexts = service.sequences("SIMPLE_SEQ", "SIMPLE_SEQ2");
        BigDecimal cur = service.sequence(false,"SIMPLE_SEQ");
        DataRow curs = service.sequences(false, "SIMPLE_SEQ", "SIMPLE_SEQ2");
*/

        row = new DataRow();
        row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        row.put("NAME", "N");
        //当前时间，如果要适配多种数据库环境尽量用SQL_BUILD_IN_VALUE,如果数据库明确可以写以根据不同数据库写成: row.put("REG_TIME","${now()}"); sysdate,getdate()等等
        row.put("REG_TIME", JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        row.put("REG_TIME", new Date());
        ConfigStore configs = new DefaultConfigStore();
        qty = service.insert(table, row, configs);
        List<Run> runs = configs.runs();
        for (Run run:runs){
            System.out.println("无占位符 sql:"+run.getFinalQuery(false));
            System.out.println("占位符 sql:"+run.getFinalQuery());
            System.out.println("sql values:"+run.getValues());
        }
        set = service.querys(table);
        row = new DataRow();
        row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        row.put("NAME", "N");
        row.put("REG_TIME", new java.sql.Timestamp(System.currentTimeMillis()));
        qty = service.insert(table, row);

        row = new DataRow();
        row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        row.put("NAME", "N");
        row.put("REG_TIME", new java.sql.Date(System.currentTimeMillis()));
        qty = service.insert(table, row);

        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        row = new DataRow();
        row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        row.put("NAME", "N");
        row.put("REG_TIME", new java.util.Date());
        qty = service.insert(table, row);
        DataSet tmps = service.querys(table);
        tmps.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        service.insert(table, tmps);

        //日期类型 插入String 值  如果不开启IS_AUTO_CHECK_METADATA会抛出异常
        row = new DataRow();
        row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
        row.put("NAME", "N");
        row.put("REG_TIME", DateUtil.format("yyyy-MM-dd HH:mm:ss"));
        qty = service.insert(table, row);

        log.warn(LogUtil.format("[单行插入][影响行数:{}][生成主键:{}]", 36), qty, row.getId());
        Assertions.assertEquals(qty , 1);

        set = new DataSet();
        for(int i=1; i<10; i++){
            row = new DataRow();
            row.put("ID", "${SIMPLE_SEQ.NEXTVAL}");
            row.put("NAME", "N"+i);
            set.add(row);
        }
        qty = service.insert(table, set);
        //默认情况下多行插入不返回序列号
        log.warn(LogUtil.format("[批量插入][影响行数:{}][默认情况下多行插入不返回序列号]", 36), qty);
        Assertions.assertEquals(qty , set.size());

        //如果需要返回序列号,在插入数据前会从数据库中提取序列值
        OracleAdapter.IS_GET_SEQUENCE_VALUE_BEFORE_INSERT = true;
        qty = service.insert(table, set);
        log.warn(LogUtil.format("[批量插入][影响行数:{}][生成主键:{}]", 36), qty, set.getStrings("ID"));
        Assertions.assertEquals(qty , set.size());



        set = service.querys("CRM_USER(ID,NAME)");
        log.warn(LogUtil.format("[批量插入][result:{}]", 36), set.toJSON());



        //查询全部数据
        set = service.querys(table);
        log.warn(LogUtil.format("[query result][查询数量:{}]", 36), set.size());
        log.warn("[多行查询数据]:{}",set.toJSON());
        long count = service.count(table);
        Assertions.assertEquals(set.size() , count);

        //只查一行
        row = service.query(table, "ORDER BY ID");
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);

        //查最后一行
        row = service.query(table, "ORDER BY ID DESC");
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);

        //更新
        //put覆盖了Map.put返回Object
        row.put("NAME", "SAVE NAME");

        //set由DataRow声明实现返回DataRow可以链式操作
        row.set("CODE", "SAVE CODE").set("DATA_VERSION", 1.2);

        //save根据是否有主键来判断insert | update
        //可以指定SAVE哪一列
        service.save(row, "NAME");
        service.save(row);
        row.put("NAME", "UPDATE NAME");

        /*
         * 注意这里的page一般不手工创建，而是通过AnylineController中的condition自动构造
         * service.querys("CRM_USER", condition(true, "ID:id","NAME:%name%", TYPE_CODE:[type]), "AGE:>=age");
         * true:表示分页 或者提供int 表示每页多少行
         * ID:表示数据表中的列
         * id:表示http提交的参数名
         * [type]:表示数组
         * */

        //分页查询
        //每页3行,当前第2页(下标从1开始)
        PageNavi page = new DefaultPageNavi(2, 3);

        //无论是否分页 都返回相同结构的DataSet
        set = service.querys(table, page);
        log.warn(LogUtil.format("[分页查询][共{}行 第{}/{}页]", 36), page.getTotalRow(), page.getCurPage(), page.getTotalPage());
        log.warn(set.toJSON());

        //模糊查询
        set = service.querys("CRM_USER", "NAME:%N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:%N");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());

        //其他条件查询
        //in
        List<Integer> in = new ArrayList<>();
        in.add(1);
        in.add(2);
        in.add(3);
        ConfigStore condition = new DefaultConfigStore();
        condition.ands("ID", in);

        //not in
        condition.and(Compare.NOT_IN, "NAME", "N1");
        List<Integer> notin = new ArrayList<>();
        notin.add(10);
        notin.add(20);
        notin.add(30);
        condition.and(Compare.NOT_IN, "ID", notin);

        //between
        List<Integer> between = new ArrayList<>();
        between.add(1);
        between.add(10);
        condition.and(Compare.BETWEEN, "ID", between);

        // >=
        condition.and(Compare.GREAT_EQUAL, "ID", "1");

        //前缀
        condition.and(Compare.LIKE_PREFIX, "NAME", "N");

        set = service.querys("CRM_USER", condition);
        log.warn(LogUtil.format("[后台构建查询条件][result:{}]", 36), set.toJSON());
        //Assertions.assertEquals(set.size() , 2);

        qty = service.count(table);
        log.warn(LogUtil.format("[总数统计][count:{}]", 36), qty);
        Assertions.assertEquals(qty , qty);

        //根据默认主键ID更新
        row.put("CODE",1001);
        //默认情况下 更新过的列 会参与UPDATE
        qty = service.update(row);
        log.warn(LogUtil.format("[根据主键更新内容有变化的化][count:{}]", 36), qty);


        //根据临时主键更新,注意这里更改了主键后ID就成了非主键，但未显式指定更新ID的情况下,ID不参与UPDATE
        row.setPrimaryKey("NAME");
        qty = service.update(row);
        log.warn(LogUtil.format("[根据临时主键更新][count:{}]", 36), qty);

        //显示指定更新列的情况下才会更新主键与默认主键
        qty = service.update(row,"NAME","CODE","ID");
        log.warn(LogUtil.format("[更新指定列][count:{}]", 36), qty);

        //根据条件更新
        ConfigStore store = new DefaultConfigStore();
        store.and(Compare.GREAT, "ID", "1")
                
                .and(" CODE > 1")
                .and("NAME IS NOT NULL");
        qty = service.update(row, store);
        log.warn(LogUtil.format("[根据条件更新][count:{}]", 36), qty);


        qty = service.delete(set);
        log.warn("[根据ID删除集合][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        //根据主键删除
        qty = service.delete(row);
        log.warn("[根据ID删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, 1);

        set = service.querys(table, "ID:1");
        qty = service.delete(table, "ID","1");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        set = service.querys(table, "ID IN(2,3)");
        qty = service.deletes(table, "ID","2","3");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());
    }

    @Test
    public void sql(){
        String sql = "SELECT * FROM CRM_USER WHERE ID > 0 AND CODE = #{CODE} AND ID > #{MAX}";
        ConfigStore configs = new DefaultConfigStore();
        configs.param("CODE","1");
        configs.and("NAME","ZH");
        service.querys(sql, configs, "MAX:100");

    }
    @Test
    public void help() throws Exception{
        Connection con = jdbc.getDataSource().getConnection();
        String schema = con.getSchema();
        String catalog = con.getCatalog();
        System.out.println("\n--------------[metadata]------------------------");
        System.out.println("catalog:"+con.getCatalog());
        System.out.println("schema:"+con.getSchema());
        ResultSet set = con.getMetaData().getTables(null, null, table, "TABLE".split(","));
        ResultSetMetaData md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[table metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",20) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }
        set = jdbc.getDataSource().getConnection().getMetaData().getColumns(null, null, null, null);
        md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[column metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",37) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }

    }
}
